package com.library.dao.db;

import com.library.dao.BorrowRepository;
import com.library.entity.BorrowRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

@Repository
public class jdbcBorrowRequestRepository implements BorrowRepository {
    private final JdbcTemplate jdbcTemplate;
    private final JdbcOperations jdbc;

    @Autowired
    public jdbcBorrowRequestRepository(JdbcTemplate jdbcTemplate, JdbcOperations jdbc) {
        this.jdbcTemplate = jdbcTemplate;
        this.jdbc = jdbc;
    }

    public static class BorrowRequestRowMapper implements RowMapper<BorrowRequest> {
        public BorrowRequest mapRow(ResultSet rs,int rowNum)throws SQLException{
            BorrowRequest br = new BorrowRequest();
            br.setId(rs.getLong("id"));
            br.setUserId(rs.getLong("userId"));
            br.setBookId(rs.getLong("bookId"));
            br.setStatus(rs.getString("status"));
            br.setRequestTime(rs.getDate("requestTime"));   // 非空

            // 下面三列可能 NULL → 直接赋值即可，JDBC 会返回 null
            br.setApprovedTime(rs.getDate("approvedTime"));
            br.setReturnTime(rs.getDate("returnTime"));
            br.setApproverId((Long) rs.getObject("approverId")); // 保险写法
            return br;
        }
    }

    @Override
    public List<BorrowRequest> findAll(long userId) {
        return jdbc.query("select * from borrowRequests where userId=? order by requestTime",new BorrowRequestRowMapper(),userId);
    }

    @Override
    public List<BorrowRequest> findByPage(int offset,int limit){
        return jdbc.query(
                "select * from Admins where status=PENDING order by requestTime limit ? offset ?",
                new BorrowRequestRowMapper(),limit,offset
        );
    }

    @Override
    public List<BorrowRequest> findByUserName(String userName){
        return jdbc.query("select br.* from borrowRequests br " +
                "join users u on br.userId=u.id where u.userName=?",
                new BorrowRequestRowMapper(),userName);
    }

    @Override
    public List<BorrowRequest> findByApproverName(String approverName){
        return jdbc.query("select br.* from borrowRequests br" +
                        "join Admins A on br.approverId=a.id where a.userName=?",
                new BorrowRequestRowMapper(), approverName);
    }

    @Override
    public List<BorrowRequest> findByISBN(String ISBN){
        return jdbc.query("select br.* from borrowRequests br" +
                "join books b on br.bookId=b.id where b.isbn=?",
                new BorrowRequestRowMapper(), ISBN);
    }

    public void addBorrowRequest(List<BorrowRequest> borrowRequest){
        for(BorrowRequest br : borrowRequest) {
            jdbc.update("insert into borrowRequests(userId,bookId,requestTime)" +
                            "values(?,?,?)", br.getUserId(), br.getBookId()
                    , br.getRequestTime());
        }
    }

    @Override
    public int countBorrowRequest(){
        return jdbc.queryForObject("select count(*) from borrowRequests",Integer.class);
    }

    @Override
    public void agreeBorrowRequest(long id,Date approvedTime){
        jdbc.update("update borrowRequests set status='APPROVED',approvedTime=?" +
                        " where id=?", approvedTime,id);
    }

    @Override
    public void rejectBorrowRequest(long id){
        jdbc.update("update borrowRequests set status='REJECTED' where id=?",id);
    }

    @Override
    public void returnBorrowRequest(long id,Date returnTime){
        jdbc.update("update borrowRequests set status='RETURNED',returnTime=? " +
                "where id=?",returnTime,id);
    }

    @Override
    public void deleteBorrowRequest(long id){
        jdbc.update("delete from borrowRequests where id=?",id);
    }
}
